package com.school.utils;

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

public class DbHelper<T>
{
    static String url = "jdbc:mysql://localhost:3306/school_snack?characterEncoding=utf-8";
    static String user = "root";
    static String password = "root";
    static DataSource dbPool;
    static ThreadLocal<Connection> currConnection = new ThreadLocal<Connection>();

    static
    {
        com.mchange.v2.c3p0.ComboPooledDataSource c3p0Pool = new com.mchange.v2.c3p0.ComboPooledDataSource();
        try
        {
            c3p0Pool.setDriverClass("com.mysql.jdbc.Driver");
            c3p0Pool.setJdbcUrl(url);
            c3p0Pool.setUser(user);
            c3p0Pool.setPassword(password);
            c3p0Pool.setInitialPoolSize(10);
            c3p0Pool.setMaxPoolSize(50);
        }
        catch (PropertyVetoException e)
        {
            e.printStackTrace();
        }
        dbPool = c3p0Pool;
    }

    public Connection getConnection() throws SQLException
    {
        Connection conn = null;
        conn = currConnection.get();
        if (conn == null)
        {
            conn = dbPool.getConnection();
            currConnection.set(conn);
        }
        return conn;
    }

    // �ر����ݿ�����
    public void closeConn()
    {
        try
        {
            this.getConnection().close();
            currConnection.remove();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
    }

    public void beginTranscaction() throws SQLException
    {
        this.getConnection().setAutoCommit(false);
    }

    public void commit() throws SQLException
    {
        this.getConnection().commit();
    }

    public void rollback() throws SQLException
    {
        this.getConnection().rollback();
    }

    // ��ɾ��
    public int executeUpdate(String sql, Object... args)
    {
        Connection conn = null;
        PreparedStatement pst = null;
        try
        {
            conn = this.getConnection();
            pst = conn.prepareStatement(sql);
            if (args != null)
            {
                for (int i = 0; i < args.length; i++)
                {
                    pst.setObject(i + 1, args[i]);
                }
            }
            int rows = pst.executeUpdate();
            return rows;
        }
        catch (SQLException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        finally
        {
            // close(conn);
            close(pst);
        }
        return -1;
    }

    // ��ѯ
    public static interface RowMapper
    {
        public Object rowMapper(ResultSet rs) throws SQLException;
    }

    public List<Object> executeQuery(String sql, RowMapper mapper,
            Object... args)
    {
        List<Object> list = new ArrayList<Object>();
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try
        {
            conn = this.getConnection();
            pst = conn.prepareStatement(sql);
            if (args != null)
            {
                for (int i = 0; i < args.length; i++)
                {
                    pst.setObject(i + 1, args[i]);
                }
                rs = pst.executeQuery();
                while (rs.next())
                {
                    Object result = mapper.rowMapper(rs);
                    list.add(result);
                }
            }
        }
        catch (SQLException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        finally
        {
            // close(conn);
            close(pst);
            close(rs);
        }
        return list;
    }

    // �ۺϲ�ѯ
    /*
     * public int executeScalar(String sql,Object...args){ List<Object> list=new
     * ArrayList<Object>(); Connection conn=null; PreparedStatement pst=null;
     * ResultSet rs=null; try { conn=this.getConnection();
     * pst=conn.prepareStatement(sql); if(args!=null && args.length>0){ for(int
     * i=0;i<args.length;i++) { pst.setObject(i+1, args[i]); } }
     * rs=pst.executeQuery(); if(rs.next()){ return rs.getInt(1); }
     * 
     * } catch (SQLException e) { // TODO Auto-generated catch block
     * e.printStackTrace(); } finally{ // close(conn); close(pst); close(rs); }
     * return 0; }
     */

    public void close(ResultSet rs)
    {
        if (rs != null)
        {
            try
            {
                rs.close();
            }
            catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
    }

    public void close(PreparedStatement pst)
    {
        if (pst != null)
        {
            try
            {
                pst.close();
            }
            catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
    }
}
